package ddd.ajava.chapter8.question1;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import ddd.ajava.chapter8.question2.Student;

public class CommandControl {
	private DatabaseDAO db=new DatabaseDAO();
	public static void main(String[] args) {
		Scanner sc=new Scanner(System.in);
		int control=0;
		CommandControl cc=new CommandControl();
		while(true){
			System.out.println("请输入命令:1显示所有学生信息  2新增 3删除 4修改 5查找 6退出");
			control=sc.nextInt();
			switch(control){
			case 1:
				cc.showAllStudent();
				break;
			case 2:
				cc.addStudent();
				break;
			case 3:
				cc.deleteStudent();
				break;
			case 4:
				cc.updateStudent();
				break;
			case 5:
				cc.selectStudent();
				break;
			case 6:
				System.exit(0);
			}
		}
	}
	
	public void showAllStudent(){
		String sql="select * from student";
		ResultSet rs=null;
		rs=db.executeSelectSQL(sql);
		try {
			while(rs.next()){
				System.out.println("学号："+rs.getString(1)+"  姓名："+rs.getString(2)+"  年级："+rs.getString(3)+"  性别："+rs.getString(4)+"  专业："+rs.getString(5)+"  学院："+rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void addStudent(){
		String sql="insert into student values(null,?,?,?,?,?);";
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入姓名");
		String name=sc.nextLine();
		System.out.println("请输入年级");
		String grade=sc.nextLine();
		System.out.println("请输入性别");
		String sex=sc.nextLine();
		System.out.println("请输入专业");
		String major=sc.nextLine();
		System.out.println("请输入学院");
		String academy=sc.nextLine();
		int count=db.executeUpdateSQL(sql,name,grade,sex,major,academy);
		if(count>0){
			System.out.println("插入成功！");
		}
	}
	
	public void deleteStudent(){
		String sql="delete from student where id=?";
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入查找学生的学号：");
		int id=sc.nextInt();
		int count=db.executeUpdateSQL(sql, id);
		if(count>0){
			System.out.println("成功删除学号为 "+id+" 的学生");
		}
	}
	
	public void updateStudent(){
		String sql="update student set name=?,grade=?,sex=?,major=?,academy=? where id=?";
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入修改学生的学号：");
		int id=Integer.parseInt(sc.nextLine());
		System.out.println("请输入姓名");
		String name=sc.nextLine();
		System.out.println("请输入年级");
		String grade=sc.nextLine();
		System.out.println("请输入性别");
		String sex=sc.nextLine();
		System.out.println("请输入专业");
		String major=sc.nextLine();
		System.out.println("请输入学院");
		String academy=sc.nextLine();
		int count=db.executeUpdateSQL(sql, name,grade,sex,major,academy,id);
		if(count>0){
			System.out.println("成功修改学号为 "+id+" 的学生");
		}
	}
	
	public void selectStudent(){
		List<Student> stuList=new ArrayList<Student>();
		ResultSet rs=null;
		String sql="select * from student where id=?";
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入查找学生的学号：");
		int id=sc.nextInt();
		rs=db.executeSelectSQL(sql, id);
		try {
			while(rs.next()){
				int idNo=rs.getInt(1);
				String name=rs.getString(2);
				String grade=rs.getString(3);
				String sex=rs.getString(4);
				String major=rs.getString(5);
				String academy=rs.getString(6);
				Student stu=new Student(idNo,name,grade,sex,major,academy);
				stuList.add(stu);
				System.out.println(stu.toString());
//				System.out.println("学号："+idNo+"  姓名："+name+"  年级："+grade+"  性别："+sex+"  专业："+major+"  学院："+academy);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
